#include "ChessDatabase.h"
#include <stdexcept>
#include <iostream>

ChessDatabase::ChessDatabase(const std::string& db_path) {
    if (sqlite3_open(db_path.c_str(), &db) != SQLITE_OK) {
        throw std::runtime_error("无法打开数据库: " + std::string(sqlite3_errmsg(db)));
    }
    create_table();
}

ChessDatabase::~ChessDatabase() {
    sqlite3_close(db);
}

void ChessDatabase::create_table() {
    const char* sql = R"(
		CREATE TABLE IF NOT EXISTS chess_pieces (
			num INTEGER PRIMARY KEY AUTOINCREMENT,
			x REAL NOT NULL,
			y REAL NOT NULL,
			color TEXT CHECK(color IN ('red', 'black')),
			type TEXT NOT NULL,
			hu1 REAL NOT NULL,
			hu2 REAL NOT NULL,
			hu3 REAL NOT NULL,
			hu4 REAL NOT NULL,
			hu5 REAL NOT NULL,
			hu6 REAL NOT NULL,
			hu7 REAL NOT NULL,
			area REAL NOT NULL,
			perimeter REAL NOT NULL,
			aspect_ratio REAL NOT NULL,
			proj_hist TEXT NOT NULL,
			zd1 REAL NOT NULL,
			zd2 REAL NOT NULL,
			zd3 REAL NOT NULL,
			zd4 REAL NOT NULL,
			zd5 REAL NOT NULL
		);
    )";

        char* errMsg = nullptr;
        if (sqlite3_exec(db, sql, nullptr, nullptr, &errMsg) != SQLITE_OK) {
            std::string error = "创建表失败: " + std::string(errMsg);
            sqlite3_free(errMsg);
            throw std::runtime_error(error);
        }
}

void ChessDatabase::addChessPiece(const ChessPiece& piece) {
	const char* sql = R"(
        INSERT INTO chess_pieces (
            x, y, color, type, 
            hu1, hu2, hu3, hu4, hu5, hu6, hu7,
            area, perimeter, aspect_ratio,
            proj_hist, zd1, zd2, zd3, zd4, zd5
        ) VALUES (
            ?, ?, ?, ?, 
            ?, ?, ?, ?, ?, ?, ?,
            ?, ?, ?,
            ?, ?, ?, ?, ?, ?
        );
    )";
        sqlite3_stmt* stmt;
        if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
            throw std::runtime_error("准备SQL语句失败");
        }

// 绑定基础参数
    sqlite3_bind_double(stmt, 1, piece.x);
    sqlite3_bind_double(stmt, 2, piece.y);
    sqlite3_bind_text(stmt, 3, piece.color.c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 4, piece.type.c_str(), -1, SQLITE_STATIC);

    // 绑定Hu矩（索引5-11）
    for (int i = 0; i < 7; ++i) {
        sqlite3_bind_double(stmt, 5 + i, piece.huMoments[i]);
    }

    // 绑定几何特征（索引12-14）
    sqlite3_bind_double(stmt, 12, piece.area);
    sqlite3_bind_double(stmt, 13, piece.perimeter);
    sqlite3_bind_double(stmt, 14, piece.aspectRatio);

    // 序列化投影直方图（索引15）
    nlohmann::json j_hist(piece.projHist);//直方图要用json格式
    std::string hist_str = j_hist.dump();
    sqlite3_bind_text(stmt, 15, hist_str.c_str(), -1, SQLITE_TRANSIENT);

    // 绑定区域密度（索引16-20）
    for (int i = 0; i < 5; ++i) {
        sqlite3_bind_double(stmt, 16 + i, piece.zoneDensity[i]);
    }
        if (sqlite3_step(stmt) != SQLITE_DONE) {
            sqlite3_finalize(stmt);
            throw std::runtime_error("插入数据失败: " + std::string(sqlite3_errmsg(db)));
        }

        sqlite3_finalize(stmt);
}

void ChessDatabase::printAllChessPieces() {
    const char* sql = "SELECT * FROM chess_pieces;";
    sqlite3_stmt* stmt;
    
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
        throw std::runtime_error("准备查询语句失败: " + std::string(sqlite3_errmsg(db)));
    }

    try {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 基础字段（索引0-4）
            int num = sqlite3_column_int(stmt, 0);
            double x = sqlite3_column_double(stmt, 1);
            double y = sqlite3_column_double(stmt, 2);
            const char* color = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
            const char* type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 4));
            // Hu矩（索引5-11）
            std::array<double, 7> huMoments;
            for (int i = 0; i < 7; ++i) {
                huMoments[i] = sqlite3_column_double(stmt, 5 + i);
            }
            // 几何特征（索引12-14）
            double area = sqlite3_column_double(stmt, 12);
            double perimeter = sqlite3_column_double(stmt, 13);
            double aspectRatio = sqlite3_column_double(stmt, 14);
            // 投影直方图（索引15）
            std::vector<double> projHist;
            const char* histJson = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 15));
            if (histJson != nullptr) {
                try {
                    projHist = nlohmann::json::parse(histJson).get<std::vector<double>>();
                } catch (const nlohmann::json::exception& e) {
                    std::cerr << "JSON解析错误: " << e.what() << std::endl;
                }
            }
            // 区域密度（索引16-20）
            std::array<double, 5> zoneDensity;
            for (int i = 0; i < 5; ++i) {
                zoneDensity[i] = sqlite3_column_double(stmt, 16 + i);
            }
            // 打印所有字段
            std::cout << "----------------------------------------\n";
            std::cout << "编号: " << num 
                      << " | 坐标: (" << x << ", " << y << ")"
                      << " | 颜色: " << color 
                      << " | 类型: " << type << "\n";
            
            std::cout << "Hu矩: ";
            for (double hu : huMoments) std::cout << hu << " ";
            
            std::cout << "\n几何特征: 面积=" << area 
                      << " 周长=" << perimeter 
                      << " 宽高比=" << aspectRatio << "\n";
            
            std::cout << "投影直方图: ";
            for (double val : projHist) std::cout << val << " ";
            
            std::cout << "\n区域密度: ";
            for (double zd : zoneDensity) std::cout << zd << " ";
            std::cout << "\n----------------------------------------\n";
        }
    } catch (...) {
        sqlite3_finalize(stmt);
        throw;
    }
    sqlite3_finalize(stmt);
}
/*
std::string ChessDatabase::matchChessPiece(const ChessPiece& piece) {
const char* sql = "SELECT type, hu1, hu2, hu3, hu4, hu5, hu6, hu7 FROM chess_pieces WHERE color = ?;";
    sqlite3_stmt* stmt;
 
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
        throw std::runtime_error("准备查询语句失败");
    }
 
    // 绑定颜色参数
    sqlite3_bind_text(stmt, 1, piece.color.c_str(), -1, SQLITE_STATIC);
 
    std::string bestType;
    double minDistance = std::numeric_limits<double>::max();
    int matchCount = 0;
 
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        const char* type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
        std::array<double, 7> dbHuMoments;
        
        // 提取数据库中的Hu矩
        for (int i = 0; i < 7; ++i) {
            dbHuMoments[i] = sqlite3_column_double(stmt, 1 + i);
        }
 
        // 计算欧氏距离
        double distance = 0.0;
        for (int i = 0; i < 7; ++i) {
            double diff = piece.huMoments[i] - dbHuMoments[i];
            distance += diff * diff;
        }
        distance = sqrt(distance);
 
        // 更新最佳匹配
        if (distance < minDistance) {
            minDistance = distance;
            bestType = type;
        }
        matchCount++;
    }
 
    sqlite3_finalize(stmt);
 
    if (matchCount == 0) {
        throw std::runtime_error("未找到同颜色棋子记录");
    }
 
    return bestType;
}
*/